clear
set more off 

********************************************************************************
// ZCTA level data
********************************************************************************

* Merge data at the ZCTA level

import delimited "zip5_pcsav31.csv", delimiter(",") clear
ren zip5c5 zipcode
ren pcsac11 pcsa 
gen re = real(zipcode)
drop if re==.
drop re
destring zipcode, replace
save "zip_pcsa.dta", replace
merge 1:1 zipcode using "ZipToZCTA.dta"
keep if _merge==3
drop _merge
merge m:1 zipcode using "ZipcodeToCity.dta"
drop if _merge==2
drop _merge
drop zipcode
duplicates drop zcta5a, force
save "zcta_pcsa.dta", replace

use "UrgentCare_R3.dta", clear
gen n_urgentcare = 1 if ucc==1
gen n_hospaffucc = 1 if hospaff_ucc==1
gen n_hospaffucc_geo = 1 if hospaff_ucc==1 & geo_tag==1
gen n_hospaffucc_geo2 = 1 if hospaff_ucc==1 & geo_tag2==1
collapse (sum) n_urgentcare n_hospaffucc n_hospaffucc_geo n_hospaffucc_geo2, by(zcta5a year)
tempfile ucc_zcta
save `ucc_zcta'

use "HospitalsGeocoded.dta", clear
gen n_hospitals = 1
collapse (sum) n_hospitals any_emergency acute_care childrens critical psychiatric, by(zcta5a year)
tempfile hosp_zcta
save `hosp_zcta'

use "IPUMSAllDemog.dta", clear
expand 13
bysort zcta5a: gen year = 2004+_n

merge m:1 zcta5a using "zcta_pcsa.dta"
keep if _merge==3
drop _merge
merge 1:1 zcta5a year using `ucc_zcta'
drop if _merge==2
drop _merge
replace n_urgentcare=0 if n_urgentcare==.
replace n_hospaffucc=0 if n_hospaffucc==.
replace n_hospaffucc_geo=0 if n_hospaffucc_geo==.
replace n_hospaffucc_geo2=0 if n_hospaffucc_geo2==.
qui ds, has(varl *is_*)
local var_list `r(varlist)'
foreach v of local var_list{
	replace `v' = 0 if `v'==.
}

merge 1:1 zcta5a year using `hosp_zcta'
drop if _merge==2
drop _merge
local vars "any_emergency acute_care childrens critical psychiatric"
foreach v of local vars{
	replace `v' = 0 if `v'==.
}
merge m:m zcta5a using "zcta_county_rel.dta", keep(3) nogen
merge m:1 zcta5a using "IPUMSHousing.dta"
drop if _merge==2
drop _merge
merge m:1 zcta5a using "IPUMSPopGrowth.dta"
drop if _merge==2
drop _merge
merge m:1 zcta5a using "IPUMSRentZCTA.dta"
drop if _merge==2
drop _merge
merge m:1 zcta5a using "IPUMSMobilityZCTA.dta"
drop if _merge==2
drop _merge
merge m:1 zcta5a using "CMSWageIndex.dta"
drop if _merge==2
drop _merge
merge m:1 county state using "SVIDataCounty.dta"
drop if _merge==2
drop _merge
replace n_hospitals = 0 if n_hospitals==.
merge m:1 zcta5a using "ZCTAToRural.dta"
drop if _merge==2
drop _merge

save "ZCTALevelData_R2.dta", replace

********************************************************************************
// HSA level data
********************************************************************************

use "ZCTALevelData_R2.dta", clear
keep if year==2015
gen gte_highschool = highschool + some_college + bachelor_higher
duplicates drop zcta5a, force
tempfile zcta2015
save `zcta2015'

use "ZiptoZCTA.dta", clear
merge m:1 zcta5a using `zcta2015'
keep if _merge==3
drop _merge
merge 1:1 zipcode using "ZipHsaHrr15.dta"
keep if _merge==3
drop _merge

tempfile base
save `base'

collapse (mean) nonhisp_white nonhisp_black hispanic income_pc nonhisp_other female age_65 uninsured medicaid medicare cms_wage_index rpl_themes rural gte_highschool [w=tot_pop], by(hsanum)
tempfile demogs
save `demogs'

use `base', clear
collapse (sum) n_urgentcare n_hospitals n_hospaffucc n_hospaffucc_geo n_hospaffucc_geo2 any_emergency tot_pop (firstnm) state county, by(hsanum)
merge 1:1 hsanum using `demogs', keep(3) nogen
save "HSALevelData_R2.dta", replace